package com.wuseguang.springcloud.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.wuseguang.springcloud.Classcardservice.ClassCardFieldService;
import com.wuseguang.springcloud.Classcardservice.ClassCardService;
import com.wuseguang.springcloud.JdbcTemplate.WXJdbc;
import com.wuseguang.springcloud.dao.jdbc.ClassRollCallJdbc;
import com.wuseguang.springcloud.service.ClassRollCallService;
import com.wuseguang.springcloud.util.CodeUtil;
import com.wuseguang.springcloud.util.DateUtil;
import com.wuseguang.springcloud.util.PinYinUtils;
import com.wuseguang.springcloud.util.Result;
import com.wuseguang.springcloud.util.WeChat.TemplateTools;
import com.wuseguang.springcloud.util.WeChat.WXUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

@Service
public class ClassRollCallServiceImpl implements ClassRollCallService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private ClassRollCallJdbc jdbc;

    @Autowired
    private WXJdbc wXJdbc;

    @Autowired
    private ClassCardService classCardService;


    @Autowired
    private ClassCardFieldService service;

    /**
     * @描述 查学校所有班级人数(小程序教师端)
     * @创建人 zm
     * @创建时间 2020/5/15
     */
    @Override
    public Result queryClassSize(Integer schoolId, String admin, String teacherId) {
        String sql = "SELECT id,name,class_icon,(SELECT COUNT(1) FROM student WHERE class_id=classes.id) count FROM  classes WHERE school_id=? AND graduation=0 ";
        if ("0".equals(admin)) {
            return new Result(jdbc.queryListMap(sql, schoolId));
        }
        String classIds = jdbc.findClassIds(schoolId + "", teacherId);
        sql = "SELECT id,name,class_icon,(SELECT COUNT(1) FROM student WHERE class_id=classes.id) count FROM  classes WHERE id in (" + classIds + ")";
        return new Result(jdbc.queryListMap(sql));
    }

    /**
     * @描述 课堂点名节次查询(小程序教师端)
     * @创建人 zm
     * @创建时间 2020/5/15
     */
    @Override
    public Result queryClassStanza(Integer schoolId, Integer classId, String time) {
        String sql = "SELECT id,class_id,name callName,rolltime,school_id FROM class_rollcall WHERE school_id =? AND class_id=? AND TO_DAYS(?) = TO_DAYS(add_time) ";
        if (StringUtils.isEmpty(time)) {//默认查当天日期
            time = DateUtil.gettime();
        }
        List<Map<String, Object>> list = jdbc.queryListMap(sql, schoolId, classId, time);
        /*if ((list != null || list.size() == 0) && DateUtil.getDay(time, DateUtil.gettime()) == 0) {
            String sql1 = "INSERT INTO class_rollcall(class_id,NAME,school_id,add_time,rolltime) SELECT s.class_id,CONCAT('第',s.y+1,'节 ',s.course_name,'/',s.teacher_name) section,s.school_id,NOW(), CONCAT(start_time,'-',end_time) TIME FROM timetable s , attend_class_time  " +
                    " t WHERE s.y=t.current_section AND  s.x=DAYOFWEEK(CURRENT_TIMESTAMP())-2 AND s.school_id=t.school_id AND s.school_id=? AND s.class_id=? ORDER BY s.school_id,s.class_id,s.y ";
            jdbc.updateByParameter(sql1, schoolId, classId);
            list = jdbc.queryListMap(sql, schoolId, classId, time);
        }*/
        return new Result(list);
    }


    /**
     * @描述 根据班级id查班级人数考勤信息(小程序教师端)
     * @创建人 zm
     * @创建时间 2020/5/16
     */
    @Override
    public Result queryClassStudent(Integer schoolId, Integer classId, String time, Integer id, String name) {//学校id 班级id  时间 节次id
        String sql = "SELECT t.id,t.student_name,t.head_portrait,IF(r.type IS NULL,IF((SELECT  COUNT(1) COUNT FROM vacation WHERE  student_id=t.id AND  state=1 AND DATE(stime)<=DATE(NOW()) " +
                "  AND  DATE(etime)>= DATE(NOW()))>0,2,4),r.type) type FROM (student t ,  class_rollcall c ) LEFT JOIN  class_rollcall_student r  ON (c.id=r.rollcall_id AND r.student_id=t.id ) WHERE " +
                "   t.school_id=c.school_id AND t.class_id=c.class_id AND c.id=? AND t.school_id=? AND t.class_id=? AND t.state=0 AND TO_DAYS(c.add_time) = TO_DAYS(?) ";
        if (!StringUtils.isEmpty(name)) {
            sql += "  AND t.student_name like '%" + name + "%'";
        }
        sql += " ORDER BY CONVERT( t.student_name USING gbk )";
        List<Map<String, Object>> list = jdbc.queryListMap(sql, id, schoolId, classId, time);
        List<TreeMap<String, Object>> lists = PinYinUtils.px(list, "student_name");
        return new Result(lists);
    }

    public static void main(String[] args) {
        String str = "";
        String s = str.indexOf("/") > 0 ? str.split("/")[0] + "课" : str;
        System.out.println(s);
    }

    /**
     * @描述 批量添加学生课堂点名信息(小程序教师端)
     * @创建人 zm
     * @创建时间 2020/5/16
     */
    @Override
    @Transactional
    public Result batchAddClassAttend(String list, Integer id, Integer teacherId) {//节次id
        int i = jdbc.batchAddClassAttend(list, id, teacherId);//批量添加
        if (i > 0) {
            List<Map<String, Object>> parse = (List<Map<String, Object>>) JSON.parse(list);
            String sql = "SELECT s.name cname,c.rolltime,DATE_FORMAT(c.add_time ,'%Y-%m-%d') add_time,t.name tname,t.phone,m.name mname  FROM (classes s ,class_rollcall c) LEFT JOIN classroom m ON (m.classid=s.id) LEFT JOIN teacher t ON c.teacher_id=t.id WHERE  c.class_id=s.id AND  c.id=?";
            Map<String, Object> map1 = jdbc.queryMap(sql, id);
            //推送公众号致家长
            sql = "select p.open_id_one,p.id,p.open_id_two  from parent p  LEFT JOIN parent_student ps on(ps.parent_id=p.id)where ps.student_id=?";
            if (map1 != null && map1.size() > 0) {
                String sql1 = " SELECT name string  FROM  class_rollcall WHERE id=?";
                String str = jdbc.queryString(sql1, id);
                //获取token
                String accessToken = wXJdbc.getOfficialAccountAccessToken(0);
                for (int j = 0; j < parse.size(); j++) {
                    Map<String, Object> map = parse.get(j);
                    //家长openid
                    Map<String, Object> parent = jdbc.queryMap(sql, map.get("id"));
                    if (map.get("type") != null) {// 0出勤 1缺勤 2 请假 3迟到 4未考勤
                        int type = (int) map.get("type");
                        if (type == 1 || type == 3) {
                            //家长的openId
                            String openId = (String) parent.get("open_id_two");
                            //判断openId是否为空
                            if (!StringUtils.isEmpty(openId)) {
                                Map<String, Object> pushMap = new HashMap<>();
                                pushMap.put("first", "家长您好，您的孩子刚刚课堂点名【" + (type == 1 ? "未到" : "迟到") + "】");
                                pushMap.put("keyword1", map.get("student_name"));
                                pushMap.put("keyword2", map1.get("cname"));
                                pushMap.put("keyword3", map1.get("mname"));
                                pushMap.put("keyword4", DateUtil.getWeeks(map1.get("add_time").toString()) + "(" + (str.indexOf("/") > 0 ? str.split("/")[0] + "课" : str) + ")");
                                pushMap.put("keyword5", map1.get("tname"));
                                pushMap.put("remark", "如有疑问,请联系" + map1.get("tname") + "老师，" + map1.get("phone"));


                                //跳转小程序信息
                                JSONObject appletJson = new JSONObject();
                                appletJson.put("appid", "wx30b606bac7775eba");
                                appletJson.put("pagepath", "pages/stanzaStudent/index");

                                //解析后的数据
                                JSONObject json = TemplateTools.initializationData(pushMap);
                                //推送公众号信息
                                WXUtil.officialAccountPush(accessToken, openId, "UN6jMjHLE6fKOR4diQGj7OXdnX_YlB29uW2dJmMem6I", "", appletJson, json);
                            }
                        }
                    }
                }
            }
            return new Result();
        }
        return new Result(CodeUtil.ADD_FAILURE);
    }

    /**
     * @描述 该学校该天所有节次, 学校统计(小程序教师端)
     * @创建人 zm
     * @创建时间 2020/5/18
     */
    @Override
    public Result querySchoolStanza(Integer schoolId, String time) {
        String sql = " SELECT CONCAT('第',stanza+1,'节') name,stanza   FROM class_rollcall WHERE school_id=? AND TO_DAYS(?) =TO_DAYS(add_time) GROUP BY stanza ORDER BY stanza";
        return new Result(jdbc.queryListMap(sql, schoolId, time));
    }


    /**
     * @描述 根据日期查该天课堂点名数据统计(小程序教师端)
     * @创建人 zm
     * @创建时间 2020/5/18
     */
    @Override
    public Result queryClassStanzaList(Integer schoolId, Integer classId, String time) {
        String sql = " SELECT t.id,t.class_id,t.callName,t.rolltime,t.school_id,t.tname,updatetime,zccount,wcount,qcount,ccount,zcount,IF(wcount=0&&qcount=0&&ccount=0&&zccount=0,1,0) state FROM  (SELECT c.id,c.class_id,c.name callName,c.rolltime,c.school_id,t.name tname,DATE_FORMAT(c.updatetime,'%Y-%m-%d %H:%i') updatetime, " +
                " (SELECT COUNT(1) FROM class_rollcall_student s WHERE s.rollcall_id=c.id AND TYPE=1) wcount,(SELECT COUNT(1) FROM class_rollcall_student s WHERE s.rollcall_id=c.id AND TYPE=2) qcount,(SELECT COUNT(1) FROM class_rollcall_student s WHERE s.rollcall_id=c.id AND TYPE=3) ccount,(SELECT COUNT(1) FROM class_rollcall_student s WHERE s.rollcall_id=c.id) zcount,(SELECT COUNT(1) FROM class_rollcall_student s WHERE s.rollcall_id=c.id AND TYPE=0) zccount " +
                " FROM class_rollcall c LEFT JOIN teacher t ON (c.teacher_id=t.id) WHERE c.school_id =? AND c.class_id=? AND TO_DAYS(?) = TO_DAYS(c.add_time) ) t ";
        return new Result(jdbc.queryListMap(sql, schoolId, classId, time));
    }


    /**
     * @描述 按添加查询课堂点名统计(小程序教师端)
     * @创建人 zm
     * @创建时间 2020/5/18
     */
    @Override
    public Result queryClassStanzaCount(Integer schoolId, Integer id, String time) {//学校id  第几节次  查询的时间
        try {
            String sql = " SELECT s.class_id,SUM(CASE WHEN t.type =0 THEN 1 ELSE 0 END) AS zccount, SUM(CASE WHEN t.type =1 THEN 1 ELSE 0 END) AS qqcount, " +
                    "SUM(CASE WHEN t.type =2 THEN 1 ELSE 0 END) AS qjcount,SUM(CASE WHEN t.type =3 THEN 1 ELSE 0 END) AS cdcount,SUM(CASE WHEN t.type =4|| t.type IS NULL THEN 1 ELSE 0 END) AS wdcount, " +
                    "COUNT(c.id) zcount FROM (student s,class_rollcall  c) LEFT JOIN class_rollcall_student t ON (c.id=t.rollcall_id AND s.id=t.student_id) " +
                    " WHERE s.class_id=c.class_id AND c.school_id=?  ";
            String sql1 = " SELECT s.class_id,SUM(CASE WHEN t.type =0 THEN 1 ELSE 0 END) AS zccount, " +
                    "SUM(CASE WHEN t.type =1 THEN 1 ELSE 0 END) AS qqcount, SUM(CASE WHEN t.type =2 THEN 1 ELSE 0 END) AS qjcount, " +
                    "SUM(CASE WHEN t.type =3 THEN 1 ELSE 0 END) AS cdcount,SUM(CASE WHEN t.type =4|| t.type IS NULL THEN 1 ELSE 0 END) AS wdcount," +
                    "COUNT(c.id) zcount,(SELECT NAME FROM classes WHERE id=c.class_id) cname FROM  " +
                    "(student s,class_rollcall  c) LEFT JOIN class_rollcall_student t ON (c.id=t.rollcall_id AND s.id=t.student_id) " +
                    " WHERE s.class_id=c.class_id AND c.school_id=? ";

            StringBuilder builder = new StringBuilder(sql);//整个学校的点名统计
            StringBuilder builder1 = new StringBuilder(sql1);//每个班的课堂点名统计
            if (!StringUtils.isEmpty(id)) {
                builder.append(" AND c.stanza=").append(id);
                builder1.append(" AND c.stanza=").append(id);
            }
            builder.append(" AND TO_DAYS(?) =TO_DAYS(c.add_time) ORDER BY s.class_id ");
            builder1.append(" AND TO_DAYS(?) =TO_DAYS(c.add_time) GROUP BY c.class_id ORDER BY s.class_id ");
            Map<String, Object> map = jdbc.queryMap(builder.toString(), schoolId, time);
            List<Map<String, Object>> list = jdbc.queryListMap(builder1.toString(), schoolId, time);
            map.put("list", list);
            return new Result(map);
        } catch (Exception e) {

        }
        return new Result();
    }


    /**
     * @描述 根据学生id查学生课堂点名统计(家长端)
     * @创建人 zm
     * @创建时间 2020/5/18
     */
    @Override
    public Result queryStanzaStudentId(Integer studentId, String time) {
        String sql = " SELECT c.id,c.name,IF(t.type IS NULL,4,TYPE) type FROM student s,class_rollcall  c LEFT JOIN class_rollcall_student t ON (c.id=t.rollcall_id AND t.student_id=? ) WHERE s.class_id=c.class_id AND s.id=? AND TO_DAYS(?)=TO_DAYS(c.add_time) ";
        return new Result(jdbc.queryListMap(sql, studentId, studentId, time));
    }

    /**
     * @描述 根据学生id查学生课堂点名统计(家长端)
     * @创建人 zm
     * @创建时间 2020/5/18
     */
    @Override
    public Result queryStanzaStudentIdCount(Integer studentId, Integer type, String startTime, String endTime) {//type 1 当天 2本周 3上周 4本月 5自定义时间
        String sql = " SELECT if(SUM(CASE WHEN t.type =0 THEN 1 ELSE 0 END) is null,0,SUM(CASE WHEN t.type =0 THEN 1 ELSE 0 END)) AS zccount,if(SUM(CASE WHEN t.type =1 THEN 1 ELSE 0 END) is null,0,SUM(CASE WHEN t.type =1 THEN 1 ELSE 0 END)) AS qqcount, if(SUM(CASE WHEN t.type =2 THEN 1 ELSE 0 END) is null,0,SUM(CASE WHEN t.type =2 THEN 1 ELSE 0 END)) AS qjcount,if(SUM(CASE WHEN t.type =3 THEN 1 ELSE 0 END) is null,0,SUM(CASE WHEN t.type =3 THEN 1 ELSE 0 END)) AS cdcount, " +
                " if(SUM(CASE WHEN t.type =4|| t.type IS NULL THEN 1 ELSE 0 END) is null,0,SUM(CASE WHEN t.type =4|| t.type IS NULL THEN 1 ELSE 0 END)) AS wdcount, COUNT(c.id) AS zcount FROM student s,class_rollcall  c LEFT JOIN class_rollcall_student t ON c.id=t.rollcall_id " +
                " WHERE s.class_id=c.class_id AND s.id=?   ";
        if (type == 2) {//2本周
            sql += " AND YEARWEEK(date_format(c.add_time,'%Y-%m-%d')) = YEARWEEK(now())";
            return new Result(jdbc.queryListMap(sql, studentId));
        } else if (type == 3) {//3上周
            sql += " AND YEARWEEK(date_format(c.add_time,'%Y-%m-%d')) = YEARWEEK(now())-1";
            return new Result(jdbc.queryListMap(sql, studentId));
        } else if (type == 4) {//4本月
            sql += " AND date_format(c.add_time,'%Y-%m')=date_format(now(),'%Y-%m')";
            return new Result(jdbc.queryListMap(sql, studentId));
        } else if (type == 5) {//5自定义时间
            sql += " AND add_time  between date_format(?,'%Y-%m-%d') and date_format(?,'%Y-%m-%d') ";
            return new Result(jdbc.queryListMap(sql, studentId, startTime, endTime));
        } else {//1 当天
            sql += " AND TO_DAYS(?) =TO_DAYS(c.add_time)";
            return new Result(jdbc.queryListMap(sql, studentId, DateUtil.gettime()));
        }
    }


    /**
     * @描述 查询管理的班级
     * @创建人 zm
     * @创建时间 2020/5/28
     */
    @Override
    public Result queryManageClassByTeacherId(String teacherId, String schoolId, String admin) {//教师id和学校id
        String sql = "SELECT s.id,s.name,s.class_icon,IF(t.state IS NULL,0,t.state) state FROM classes s LEFT JOIN classroom_teaching t ON (s.id=t.class_id) WHERE s.school_id=? AND s.graduation=0 ";
        if (!"0".equals(admin)) {
            String classid = jdbc.findClassIds(schoolId, teacherId);
            sql += " AND s.id in (" + classid + ") ";
        }
        sql += " order by s.id ";
        return new Result(jdbc.queryListMap(sql, schoolId));
    }


    /**
     * @描述 修改授课状态
     * @创建人 zm
     * @创建时间 2020/5/28
     */
    @Override
    public Result updateManageClassId(Integer classId, Integer state) {//教师id和学校id
        String sql = "DELETE FROM classroom_teaching WHERE class_id=?";
        jdbc.updateByParameter(sql, classId);
        sql = "INSERT INTO `classroom_teaching` (`class_id`, `state`) VALUES(?,?) ";
        jdbc.updateByParameter(sql, classId, state);
        service.classesAndCards(classId, "teachingflag", state + "");
        classCardService.classesAndCards(classId);
        return new Result();
    }

}
